package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.School;
import lombok.extern.slf4j.Slf4j;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

@Slf4j
public final class SchoolDao {
    private static SchoolDao schoolDao = null;

    private SchoolDao() {
    }

    public static SchoolDao getInstance() {
        if (schoolDao == null) {
            schoolDao = new SchoolDao();
        }
        return schoolDao;
    }

    public static Collection<School> findAll(Connection connection,
                                             List conditions_json,
                                             Pagination pagination)
            throws SQLException {
        //创建集合类对象，用来保存所有记录代表的School对象
        Collection<School> desiredSchools = new HashSet<School>();
        //准备预编译的语句
        int totalNum = CommonDao.count(connection, "school");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT*FROM school");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            sql_select.append(pagination.toLimitClause(totalNum));
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //遍历resultSet
        while (resultSet_select.next()) {

            //根据id查找结果创建school对象
            School school = new School(
                    resultSet_select.getInt("id"),
                    resultSet_select.getString("description"),
                    resultSet_select.getString("no"),
                    resultSet_select.getString("remarks"));
            desiredSchools.add(school);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        log.debug("" + desiredSchools);
        return desiredSchools;
    }


    //根据id，查找学院
    public School find(Integer id, Connection connection) throws SQLException {

        String deleteSchool_sql = "select * from School where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建school对象
        School school = new School(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭
        JdbcHelper.close(pstmt, connection);
        return school;
    }

    //修改
    public boolean update(School school, Connection connection) throws SQLException {

        String deleteSchool_sql = "" +
                "UPDATE School SET no = ?,description = ?,remarks = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setString(1, school.getNo());
        pstmt.setString(2, school.getDescription());
        pstmt.setString(3, school.getRemarks());
        pstmt.setInt(4, school.getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }

    //添加学院
    public boolean add(School school, Connection connection) throws SQLException {

        String deleteSchool_sql = "" +
                "INSERT INTO school(no,description,remarks) VALUES" + "(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setString(1, school.getNo());
        pstmt.setString(2, school.getDescription());
        pstmt.setString(3, school.getRemarks());
        //添加学院 并判断是否添加成功
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }

    //根据school对象删除
    public boolean delete(School school, Connection connection) throws SQLException {

        String deleteSchool_sql = "delete from School where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setString(1, school.getNo());
        //根据id删除学院 并判断是否删除成功
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }

}
